﻿using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Text;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using MC.Warehouse.Core;

namespace MC.Warehouse.DAL
{
    public abstract class Repository<T,TR> : IDisposable where T : Entity.BaseEntity where TR : Models.BaseModel
    {
        #region COMMOM DATA AND FUNCTION
        const int Topload = 20, Topsearch = 10000; 
        private List<Parameter<T>> _sqlUpdateCollection;
        private List<Parameter<T>> _sqlWhereCollection;
        private Expression<Func<T, object>> _sqlSelectCols;

        protected Expression<Func<T,object>> SqlSelectCols 
        {
            get { return _sqlSelectCols ?? (_sqlSelectCols = p => null); }
            set
            {
                _sqlSelectCols = value;
            }
        }
        
        protected List<Parameter<T>> SqlWhereCollection 
        {
            get { return _sqlWhereCollection ?? (_sqlWhereCollection = new List<Parameter<T>>()); }
            set
            {
                _sqlWhereCollection = value;
            }
        }
        
        protected List<Parameter<T>> SqlUpdateCollection
        {
            get { return _sqlUpdateCollection ?? (_sqlUpdateCollection = new List<Parameter<T>>()); }
            set
            {
                _sqlUpdateCollection = value;
            }
        }

        private string GetConnection()
        {
            return Properties.Settings.Default.DataConnectionString;
        }

        public void Dispose()
        {

        }
        #endregion

        #region PUBLICE QUERY
        //completed
        protected DataTable AutoSearch()
        {
            var result = new DataTable();
            using (var sqlcon = new OleDbConnection(GetConnection()))
            {
                var sqlcmd = GetCommandForAutoLoad();
                sqlcmd.Connection = sqlcon;
                sqlcmd.CommandType = CommandType.Text;

                sqlcon.Open();
                var reader = sqlcmd.ExecuteReader();
                if (reader != null)
                {
                    result.Load(reader);

                    reader.Close();
                }
                sqlcon.Close();
            }
            return result;
        }

        //completed
        protected DataTable Search()
        {
            var result = new DataTable();
            using (var sqlcon = new OleDbConnection(GetConnection()))
            {
                var sqlcmd = GetCommandForSearch();
                sqlcmd.Connection = sqlcon;
                sqlcmd.CommandType = CommandType.Text;

                sqlcon.Open();
                IDataReader reader = sqlcmd.ExecuteReader();
                if (reader != null)
                {
                    result.Load(reader);

                    reader.Close();
                }
                sqlcon.Close();
            }
            return result;
        }

        //completed
        protected TR GetById()
        {
            var result = new DataTable();
            using (var sqlcon = new OleDbConnection(GetConnection()))
            {
                var query = new StringBuilder();
                var item = SqlWhereCollection.First();
                string colName = item.Func.Body.ToString();
                string selCols = GetQueryForSelect(SqlSelectCols);
                query.AppendFormat("SELECT {0} WHERE {1} {2}", selCols,colName,item.Oparator);

                var sqlcmd = new OleDbCommand(query.ToString(),sqlcon);
                sqlcmd.Parameters.AddWithValue("@" + colName, item.Value);

                sqlcon.Open();
                IDataReader reader = sqlcmd.ExecuteReader();
                if (reader != null)
                {
                    result.Load(reader);
                    reader.Close();
                }
                sqlcon.Close();
            }
            return CollectionHelper.CreateItem<TR>(result.Rows[0]);
        }

        //completed
        protected byte Insert()
        {
            int flag;
            using (var sqlcon = new OleDbConnection(GetConnection()))
            {
                var sqlcmd = GetCommandForInsert();
                sqlcmd.Connection = sqlcon;
                sqlcmd.CommandType = CommandType.Text;

                sqlcon.Open();
                flag = sqlcmd.ExecuteNonQuery();
                sqlcon.Close();
            }
            return (byte)flag;
        }

        //completed
        protected byte Update()
        {
            int flag;
            using (var sqlcon = new OleDbConnection(GetConnection()))
            {
                var sqlcmd = GetCommandForUpdate();
                sqlcmd.Connection = sqlcon;
                sqlcmd.CommandType = CommandType.Text;

                sqlcon.Open();
                flag = sqlcmd.ExecuteNonQuery();
                sqlcon.Close();
            }
            return (byte)flag;
        }

        //completed
        protected byte Delete()
        {
            int flag = -1;
            using (var sqlcon = new OleDbConnection(GetConnection()))
            {
                var query = new StringBuilder();
                var data = SqlWhereCollection.First();
                var memberExpression = data.Func.Body as MemberExpression;
                if (memberExpression != null)
                {
                    string colName = memberExpression.Member.Name;
                    string tblname = data.Func.Parameters[0].Type.Name;
                    query.AppendFormat("DELETE FROM {0} WHERE {1}{2}", tblname,colName,data.Oparator);

                    var sqlcmd = new OleDbCommand(query.ToString(),sqlcon);
                    sqlcmd.Parameters.AddWithValue("@"+colName, data.Value);

                    sqlcon.Open();
                    flag = sqlcmd.ExecuteNonQuery();
                }
                sqlcon.Close();
            }
            return (byte)flag;
        }
        #endregion

        #region PRIVATE FUNCTION
        private OleDbCommand GetCommandForAutoLoad()
        {
            var sqlcmd = new OleDbCommand();
            var query = new StringBuilder();
            query.AppendFormat("SELECT TOP {0} ", Topload);
            query.Append(GetQueryForSelect(SqlSelectCols));
            sqlcmd.CommandText = query.ToString();
            return sqlcmd;
        }

        private OleDbCommand GetCommandForSearch()
        {
            var sqlcmd = new OleDbCommand();
            var query = new StringBuilder();
            query.AppendFormat("SELECT TOP {0} ", Topsearch);
            query.Append(GetQueryForSelect(SqlSelectCols));
            if (SqlWhereCollection.Count>0)
            {
                query.Append(" WHERE ");
                int count = SqlWhereCollection.Count;
                for (int i = 0; i < count; i++)
                {
                    var item = SqlWhereCollection[i];
                    string colName = item.Func.Body.ToString();
                    query.Append(colName);
                    query.Append(item.Oparator);
                    if (item.Oparator.Equals(Oparator.Like) || item.Oparator.Equals(Oparator.NotLike))
                        sqlcmd.Parameters.AddWithValue("@" + colName, "%" + item.Value + "%");
                    else if (!item.Oparator.Equals(Oparator.IsNotNull) && !item.Oparator.Equals(Oparator.IsNull))
                        sqlcmd.Parameters.AddWithValue("@" + colName, item.Value);
                    if (i < count - 1)
                        query.Append(Oparator.And);
                }
            }
            sqlcmd.CommandText = query.ToString();
            return sqlcmd;
        }

        private string GetQueryForSelect(Expression<Func<T, object>> expression)
        {
            var query = new StringBuilder();
            query.Append(SelecColumns(expression));
            query.AppendFormat("FROM {0} as {1}", expression.Parameters[0].Type.Name, expression.Parameters
                [0].Name);
            return query.ToString();
        }

        private string SelecColumns(Expression<Func<T, object>> expression)
        {
            var query = new StringBuilder();
            if (expression.Body.ToString() == "null")
                query.Append("* ");
            else
            {
                var select = expression.Body.ToString();
                int start = select.IndexOf('(');
                int end = select.LastIndexOf(')');
                query.Append(select.Substring(start, end + 1 - start));
            }
            return query.ToString();
        }

        private OleDbCommand GetCommandForInsert()
        {
            var sqlcmd = new OleDbCommand();
            var select = new StringBuilder();
            var values = new StringBuilder();

            select.AppendFormat("INSERT INTO {0} (",SqlSelectCols.Parameters[0].Type.Name);

            int count = SqlUpdateCollection.Count;
            for (int i = 0; i < count; i++)
            {
                var item = SqlUpdateCollection[i];
                var memberExpression = SqlUpdateCollection[i].Func.Body as MemberExpression;
                if (memberExpression != null)
                {
                    string colName = memberExpression.Member.Name;

                    @select.Append(colName);
                    values.AppendFormat("@" + colName);
                    if (i < count - 1)
                    {
                        @select.Append(", ");
                        values.Append(", ");
                    }

                    sqlcmd.Parameters.AddWithValue("@" + colName, item.Value);
                }
            }
            select.AppendFormat(") VALUES ({0})", values);
           
            sqlcmd.CommandText = select.ToString();
            return sqlcmd;
        }

        private OleDbCommand GetCommandForUpdate()
        {
            var sqlcmd = new OleDbCommand();
            var query = new StringBuilder();
            var data = SqlWhereCollection.First();
            query.AppendFormat("UPDATE {0} as {1} SET ", data.Func.Parameters[0].Type.Name, data.Func.Parameters
                [0].Name);

            int count = SqlUpdateCollection.Count;
            for (int i = 0; i < count; i++)
            {
                var item = SqlUpdateCollection[i];
                string colName = item.Func.Body.ToString();
                query.Append(colName);
                query.Append(item.Oparator); 
                if (i < count - 1)
                    query.Append(", ");
                sqlcmd.Parameters.AddWithValue("@" + colName,  item.Value );
            }

            string id = data.Func.Body.ToString();
            query.Append(" WHERE ");
            query.Append(id);
            query.Append(data.Oparator);
            sqlcmd.Parameters.AddWithValue("@" + id, data.Value);
            sqlcmd.CommandText = query.ToString();
            return sqlcmd;
        }
        #endregion
    }
}
